package DAL.IMPL;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import ERRORS.ClientExistantException;
import ERRORS.ClientInexistantException;
import DAL.DAO.SocieteDAO;
import DAL.DTO.SocieteDTO;
import DAL.DTO.AdresseDTO;
import DAL.SQLConnection;

public class SocieteDAOImpl extends SocieteDAO {
	private static SocieteDAO instance = null;
	private SQLConnection connection = new SQLConnection();
        private ResultSet resultat;
       	
	//Constructeur du singleton
	public static SocieteDAO getInstance(){
		if(instance == null)
			instance = new SocieteDAOImpl();
		return instance;
	}

	//Méthodes
	@Override
	public SocieteDTO rechercher(long numeroClient) throws ClientInexistantException {
		try{
			this.connection.connecter();
			this.resultat = this.connection.executeSearch(  "SELECT * FROM PersonneMorales WHERE " +
                                                                        " NUMEROCLIENT = " + numeroClient);
		}catch(RuntimeException e){
			throw new RuntimeException(e);
		}
		try{
			if(!resultat.next())
				throw new ClientInexistantException();
			String nom = resultat.getString("NOM");
			String statut = resultat.getString("STATUT");
			String numeroTVA = resultat.getString("NUMEROTVA");
			String rue = resultat.getString("RUE");
			String numero = resultat.getString("NUMERO");
			int codePostal = resultat.getInt("CODEPOSTAL");
			String ville = resultat.getString("VILLE");
			String pays = resultat.getString("PAYS");
			String telephone = resultat.getString("TELEPHONE");
			String email = resultat.getString("EMAIL");
			String personneContact = resultat.getString("PERSONNECONTACT");
			/*SocieteDTO personne = new SocieteDTO(
                                numeroClient, nom, statut, numeroTVA, rue, numero,
                                codePostal, ville, pays, telephone, email, personneContact);
			return personne;*/
		}catch(SQLException e){
			throw new ClientInexistantException(e);
		}finally{
			this.connection.deconnecter();
		}
	}

	@Override
	//Complète
	public SocieteDTO rechercher(String numeroTVA) throws ClientInexistantException {
                try{
			this.connection.connecter();
			this.resultat = this.connection.executeSearch(   "SELECT * FROM PersonnesMorales WHERE " +
                                                                        "NUMEROTVA = " + "'" + numeroTVA + "'");
		}catch(RuntimeException e){
			throw new RuntimeException(e);
		}
		try{
			if(!resultat.next())
				throw new ClientInexistantException();
			long numeroClient = resultat.getLong("NUMEROCLIENT");
                        String nom = resultat.getString("NOM");
			String statut = resultat.getString("STATUT");
			String rue = resultat.getString("RUE");
			String numero = resultat.getString("NUMERO");
			int codePostal = resultat.getInt("CODEPOSTAL");
			String ville = resultat.getString("VILLE");
			String pays = resultat.getString("PAYS");
			String telephone = resultat.getString("TELEPHONE");
			String email = resultat.getString("EMAIL");
			String personneContact = resultat.getString("PERSONNECONTACT");
			/*SocieteDTO personne = new SocieteDTO(
                                numeroClient, nom, statut, numeroTVA, rue, numero,
                                codePostal, ville, pays, telephone, email, personneContact);
			return personne;*/
		}catch(SQLException e){
			throw new ClientInexistantException(e);
		}finally{
			this.connection.deconnecter();
		}
	}	

	@Override
	//Complète
	public SocieteDTO rechercher(String nom, String statut) throws ClientInexistantException {
		try{
			this.connection.connecter();
			this.resultat = this.connection.executeSearch(  "SELECT * FROM PersonnesMorales WHERE " +
                                                                        "NOM = " + "'" + nom + "' AND STATUT = " + "'" + statut + "'");
		}catch(RuntimeException e){
			throw new RuntimeException(e);
		}
                try{
			if(!resultat.next())
				throw new ClientInexistantException();
			long numeroClient = resultat.getLong("NUMEROCLIENT");
                        String numeroTVA = resultat.getString("NUMEROTVA");
			String rue = resultat.getString("RUE");
			String numero = resultat.getString("NUMERO");
			int codePostal = resultat.getInt("CODEPOSTAL");
			String ville = resultat.getString("VILLE");
			String pays = resultat.getString("PAYS");
			String telephone = resultat.getString("TELEPHONE");
			String email = resultat.getString("EMAIL");
			String personneContact = resultat.getString("PERSONNECONTACT");
			/*SocieteDTO personne = new SocieteDTO(
                                numeroClient, nom, statut, numeroTVA, rue, numero,
                                codePostal, ville, pays, telephone, email, personneContact);
			return personne;*/
		}catch(SQLException e){
			throw new ClientInexistantException(e);
		}finally{
			this.connection.deconnecter();
		}
	}	
	        
	@Override
	public SocieteDTO rechercher(AdresseDTO adresse) throws ClientInexistantException {
		try{
			this.connection.connecter();
			this.resultat = this.connection.executeSearch("SELECT * FROM PersonnesMorales WHERE" + "'" + adresse + "'");
		}catch(RuntimeException e){
			throw new RuntimeException(e);
		}
                try{
			if(!resultat.next())
				throw new ClientInexistantException();
			long numeroClient = resultat.getLong("NUMEROCLIENT");
                        String nom = resultat.getString("NOM");
			String statut = resultat.getString("STATUT");
			String numeroTVA = resultat.getString("NUMEROTVA");
			String rue = resultat.getString("RUE");
			String numero = resultat.getString("NUMERO");
			int codePostal = resultat.getInt("CODEPOSTAL");
			String ville = resultat.getString("VILLE");
			String pays = resultat.getString("PAYS");
			String telephone = resultat.getString("TELEPHONE");
			String email = resultat.getString("EMAIL");
			String personneContact = resultat.getString("PERSONNECONTACT");
			/*SocieteDTO personne = new SocieteDTO(
                                numeroClient, nom, statut, numeroTVA, rue, numero,
                                codePostal, ville, pays, telephone, email, personneContact);
			return personne;*/
		}catch(SQLException e){
			throw new ClientInexistantException(e);
		}finally{
			this.connection.deconnecter();
		}
	}	
               
        @Override
	public void enregistrer(SocieteDTO personne) throws ClientExistantException {
		try{
			this.connection.connecter();
			this.connection.executeQuery(   "INSERT INTO PersonnesMorales " +
                                                        "(NOM, STATUT, NUMEROTVA, RUE, NUMERO, " +
                                                        "CODEPOSTAL, VILLE, PAYS, TELEPHONE, EMAIL, " +
                                                        "PERSONNECONTACT) values ('" 
                                                        + personne.getNom() + "', '"
                                                        + personne.getStatut() + "', '"
                                                        + personne.getNumeroTVA() + "', '"
                                                        + personne.getAdresse().getRue() + "', '"
                                                        + personne.getAdresse().getNumero() + "', '"
                                                        + personne.getAdresse().getCodePostal() + "', '"
                                                        + personne.getAdresse().getVille() + "', '"
                                                        + personne.getAdresse().getPays() + "', '"
                                                        + personne.getTelephone() + "', '"
                                                        + personne.getEmail() + "', '"
                                                        /*+ personne.getPersonneContact() + "')"*/);
                }catch(RuntimeException e){
			throw new ClientExistantException(e);
		}finally{
			this.connection.deconnecter();
		}
	}

	@Override
	public SocieteDTO modifier(SocieteDTO personne) throws ClientInexistantException {
		try{
			this.connection.connecter();
			this.connection.executeQuery(   "UPDATE PersonnesMorales SET "
                                                        + "NOM = '" + personne.getNom() + "', "
                                                        + "STATUT = '" + personne.getStatut() + "', "
                                                        + "NUMERTVA = '" + personne.getNumeroTVA() + "', "
                                                        + "RUE = '" + personne.getAdresse().getRue() + "', "
                                                        + "NUMERO = '" + personne.getAdresse().getNumero() + "', "
                                                        + "CODEPOSTAL = '" + personne.getAdresse().getCodePostal() + "', "
                                                        + "VILLE = '" + personne.getAdresse().getVille() + "', "
                                                        + "PAYS = '" + personne.getAdresse().getPays() + "', "
                                                        + "TELEPHONE = '" + personne.getTelephone() + "', "
                                                        + "EMAIL = '" + personne.getEmail() + "', "
                                                        /*+ "PERSONNECONTACT = '" + personne.getPersonneContact() + "', "*/
                                                        + "WHERE NUMEROCLIENT = "
                                                        /*+ personne.getNumeroClient()*/);
		}catch(RuntimeException e){
			throw new ClientInexistantException(e);
		}finally{
			this.connection.deconnecter();
		}
		System.out.println("Debug: Update methode SQL");
		//recharger(personne);
		return personne;
	}

	@Override
	public void supprimer(SocieteDTO personne) throws ClientInexistantException {
		int nbLigne = 0;
		try{
			connecter();
			query =  connexion.createStatement();
			String leQuery = "DELETE FROM PersonnesMorales WHERE " +
                                "NUMEROCLIENT = " + personne.getNumeroClient();
			nbLigne = query.executeUpdate(leQuery);
			connexion.commit();
		}catch(SQLException e){
			throw new RuntimeException(e);
		}finally{
			deconnecter();
		}
		if (nbLigne == 0)
			throw new ClientInexistantException();
	}
	
	@Override
	public ArrayList<SocieteDTO> liste() {
		try{
			connecter();
			query = connexion.createStatement();
			String leQuery = "SELECT * FROM PersonnesMorales ORDER BY NOM";
			ResultSet resultat = query.executeQuery(leQuery);
			ArrayList<SocieteDTO> liste = new ArrayList<SocieteDTO>();
			while(resultat.next()){
				long numeroClient = resultat.getLong("NUMEROCLIENT");
                                String nom = resultat.getString("NOM");
                                String statut = resultat.getString("STATUT");
                                String numeroTVA = resultat.getString("NUMEROTVA");
                                String rue = resultat.getString("RUE");
                                String numero = resultat.getString("NUMERO");
                                int codePostal = resultat.getInt("CODEPOSTAL");
                                String ville = resultat.getString("VILLE");
                                String pays = resultat.getString("PAYS");
                                String telephone = resultat.getString("TELEPHONE");
                                String email = resultat.getString("EMAIL");
                                String personneContact = resultat.getString("PERSONNECONTACT");
                                SocieteDTO personne = new SocieteDTO(
                                        numeroClient, nom, statut, numeroTVA, rue, numero,
                                        codePostal, ville, pays, telephone, email, personneContact);
				liste.add(personne);
			}
			return liste;
		}catch(SQLException e){
			throw new RuntimeException(e);
		}finally{
			deconnecter();
		}
	}

	@Override
	public int nombreSocietes(){
		ResultSet resultat = null;
		try{
			connecter();
			query = connexion.createStatement();
			String leQuery = "SELECT COUNT(*) FROM PersonnesMorales";
			resultat = query.executeQuery(leQuery);
			if(!resultat.next()) return 0;
			return resultat.getInt(1);
		}catch(SQLException e){
			throw new RuntimeException(e);
		}finally{
			deconnecter();
		}
	}
}